package com.yulian.util;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;  
 
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.buffer.Buffer;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.web.RoutingContext;

public class ExportExcelUtil {
	final static InternalLogger logger = Log4JLoggerFactory.getInstance(ExportExcelUtil.class);

	
	/**
     * 设置excel的字段
     * @param context  上下文
     * @param jsonArray  传入的beanlist对象
     * @Param sheetName      一个sheet的表名
     * @param fileName       导出excel的文件名 
     * @return
     */
    public static void excelUtil(RoutingContext context,
                                 JsonArray jsonArray, 
                                 String sheetName,
                                 String fileName){ 
        //如果上下文为空则返回
        if(context==null){
            logger.info("传入的RoutingContext为空");
            return;
        }
        //如果传入的json数组为空则返回空
        if(jsonArray == null || jsonArray.size()<=0){
            logger.info("传入的json数组为空");
            context.response().end("");
            return;
        }
        //如果传入sheetName的参数为空或者“”
        if(sheetName == null || sheetName.equals("")) {
            //设置sheet名称
            sheetName="sheet1";
        }
        //如果传入fileName的参数为空或者“”
        if(fileName == null || fileName.equals("")) {
            //指定默认的文件名为时间
            Date date = new Date();
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            fileName=format.format(date);
        }
        
        int rowMaxCache = 100;
        //基于 XSSFWorkbook 生成 SXSSFWorkbook
		XSSFWorkbook xssfWb = new XSSFWorkbook();
		try (SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWb, rowMaxCache)) {
			XSSFCellStyle titleStyle = (XSSFCellStyle) workbook.createCellStyle(); 
			titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
			titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
			// 设置边框
			titleStyle.setBorderBottom(BorderStyle.THIN);
			titleStyle.setBorderLeft(BorderStyle.THIN);
			titleStyle.setBorderRight(BorderStyle.THIN);
			titleStyle.setBorderTop(BorderStyle.THIN);
			//定义工作表 SXSSFSheet
			SXSSFSheet sheet =(SXSSFSheet) workbook.createSheet(sheetName);   
			
			//创建首行
			Row tableHead = sheet.createRow(0); 
			//获取第一个json数组中的第一个json对象,取出key作为列名
			JsonObject jsObject = jsonArray.getJsonObject(0); 
			//创键excel表头中列数
			int cell = 0;
			//设置表头
			//将对应键的值插入到每一列的excel中
			Iterator<String>  iter0 = jsObject.fieldNames().iterator();  
			while (iter0.hasNext()) { 
				String key = iter0.next(); 
				try { 
					//设置excel头部的每一列
			        tableHead.createCell(cell).setCellValue(key); 
			        //cell自增
			        cell++; 
				} catch (Exception e) { 
					// Something went wrong! 
				}
			}  

			for(int i=0;i<jsonArray.size();i++){
			    //获取body JSON数组中的json
			    JsonObject jsObj = jsonArray.getJsonObject(i);
			    //创建excel中的每一个行
			    Row row = sheet.createRow(i+1);
			    int cell2 = 0;
			    //将对应键的值插入到每一列的excel中
			    Iterator<String>  iter = jsObj.fieldNames().iterator();  
				while (iter.hasNext()) { 
					String key = iter.next(); 
					try { 
						String value = jsObj.getString(key); 
						//如果value为空则赋值空字符串
			            if(value==null)
			            	value = "";
			            row.createCell(cell2).setCellValue(value);
			            cell2++;
			
					} catch (Exception e) { 
						// Something went wrong! 
					}
				}   
			}
			//创建字节输入流
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			try{
			    //将excel表的数据写入到字节流中
			    workbook.write(bos);
			}catch(IOException e){
			    throw new RuntimeException("export excel error");
			}
			//设置缓冲区
			Buffer buffer = Buffer.buffer();
			//将流中的内容写入到缓冲区中
			buffer.appendBytes(bos.toByteArray());
			//设置传输的格式
			context = setContext(context,fileName);
			context.response().end(buffer);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    } 
	
	/**
     * //设置传输的格式
     * @param context 上下文
     * @param buffer    传输的buffer
     * @param fileName  文件名
     * @return
     */
    public static RoutingContext setContext(RoutingContext context,String fileName){
        //设置传输的格式
        context.response().putHeader("content-type", "application/octet-stream;charset=UTF-8");
        context.response().putHeader("Content-Disposition", "attachment;filename="+fileName+".xls");
        context.response().putHeader("Pargam", "no-cache");
        context.response().putHeader("Cache-Control", "no-cache");
        return context;
    }
 

}
